﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;

namespace AddressBook.DAO
{
    public class Globle
    {
        public static string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

        /// <summary>
        /// 将datatable转成实体
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static List<T> FillModel<T>(DataTable dt)
        {
            List<T> l = new List<T>();
            T model = default(T);

            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Columns[0].ColumnName == "rowId")
                {
                    dt.Columns.Remove("rowId");
                }

                foreach (DataRow dr in dt.Rows)
                {
                    model = Activator.CreateInstance<T>();

                    foreach (DataColumn dc in dr.Table.Columns)
                    {
                        PropertyInfo pi = model.GetType().GetProperty(dc.ColumnName);
                        if (pi != null)
                        {
                            if (dr[dc.ColumnName] != DBNull.Value)
                                pi.SetValue(model, dr[dc.ColumnName], null);
                            else
                                pi.SetValue(model, null, null);
                        }
                    }
                    l.Add(model);
                }
            }

            return l;
        }

        /// <summary> 
        /// 将实体类转换成DataTable 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="i_objlist"></param> 
        /// <returns></returns> 
        public static DataTable FillDataTable<T>(List<T> objlist)
        {
            if (objlist == null || objlist.Count <= 0)
            {
                return null;
            }
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;

            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (T t in objlist)
            {
                if (t == null)
                {
                    continue;
                }

                row = dt.NewRow();

                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];

                    string name = pi.Name;

                    if (dt.Columns[name] == null)
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                    }

                    row[name] = pi.GetValue(t, null);
                }

                dt.Rows.Add(row);
            }
            return dt;
        }
        
        public static int ExecuteNonQuery(string sqlstr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public static int ExecuteNonQuery(string sqlstr, CommandType type)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    cmd.CommandType = type;
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public static int ExecuteNonQuery(string sqlstr, CommandType type, SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    cmd.Parameters.AddRange(parameters);
                    cmd.CommandType = type;
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetSQLDataTable(string sqlstr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetSQLDataTable(string sqlstr, CommandType type)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    cmd.CommandType = type;
                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetSQLDataTable(string sqlstr, CommandType type, SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    cmd.CommandType = type;
                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    con.Open();
                    adapter.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<T> GetSQLDataEntity<T>(string sqlstr, CommandType type, SqlParameter[] parameters)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sqlstr;
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    cmd.CommandType = type;
                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    con.Open();
                    adapter.Fill(dt);
                    return FillModel<T>(dt);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}